package com.pacifictextiles.qc.inspection.server.model.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;

import java.sql.PreparedStatement;

import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.pacifictextiles.qc.inspection.server.model.dao.IDefectDAO;
import com.pacifictextiles.qc.inspection.server.model.dbc.DatabaseConnection;
import com.pacifictextiles.qc.inspection.server.model.vo.Defect;

public class DefectDAOImpl implements IDefectDAO {

	@Override
	public boolean addDefect(Defect defect) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String insert = "INSERT INTO  `qc_inspection`.`defect` "
				+ "(`indexInRoll` ,`rollNo` ,`defectCode` ,`position` ,"
				+ "`point` ,`dateAndTime` ,`rating`) " + "VALUES "
				+ "(?,  ?,  ?,  ?,  " + "?, CURRENT_TIMESTAMP , ?);";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(insert);

			// 准备参数到查询语句中？号的位置从1到N
			pStatement.setInt(1, defect.getIndexInRoll());
			pStatement.setString(2, defect.getRollNo());
			pStatement.setInt(3, defect.getDefectCode());
			pStatement.setFloat(4, defect.getPosition());
			pStatement.setInt(5, defect.getPoint());

			// 评级在数据库里设置的是单个char
			pStatement.setString(6, String.valueOf(defect.getRating()));

			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("DefectDaoImpl:  添加defect成功" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();

			// 返回 boolean 值判断是否执行成功
			return isSuccess;

		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.print("DefectDaoImpl:  请检查插入语句是否正确"+"\n\n");
		}

		// 返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public boolean deleteDefect(String rollNo, int indexInRoll)
			throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String delete = "DELETE FROM `qc_inspection`.`defect` "
				+ "WHERE `defect`.`rollNo` = ?"
				+ "AND  `defect`.`indexInRoll`= ?";
		try {
			
			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(delete);

			// 准备参数到查询语句中？号的位置从1到N
			pStatement.setString(1, rollNo);
			pStatement.setInt(2, indexInRoll);

			// 执行预处理语句 用 validate 接受返回的 int
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("DefectDaoImpl:  删除defect成功" + "\n\n");
				isSuccess = true;
			} else {
				System.out.print("DefectDaoImpl:  找不到这一个defect" + "\n\n");
			}
			
			// 关闭数据库连接
			dbc.close();
			
			// 返回 boolean 值判断是否执行成功
			return isSuccess;

		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.print("DefectDaoImpl:  请检查删除语句是否正确" + "\n");
		}

		// 返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public Defect modifyDefect(Defect defect) {

		return null;
	}

	@Override
	public List<Defect> getDefectList(String rollNo) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		List<Defect> defectList = new LinkedList<Defect>();

		// select预处理语句中，使用?代替变量会报错
		String select = "SELECT *  FROM `defect` WHERE `rollNo` = '"+rollNo+"'";

		try {
			
			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(select);
			
			// 执行预处理语句
			rs = pStatement.executeQuery(select);

			if (rs != null) {
				while (rs.next()) {
					Defect defect = new Defect();
					
					defect.setIndexInRoll(rs.getInt("indexInRoll"));
					defect.setRollNo(rs.getString("rollNo"));
					defect.setDefectCode(rs.getInt("defectCode"));
					defect.setPosition(rs.getFloat("position"));
					defect.setPoint(rs.getInt("point"));
					defect.setDateAndTime(rs.getString("dateAndTime"));

					// 评级在数据库里设置的是单个char
					defect.setRating(rs.getString("rating").charAt(0));
					defectList.add(defect);
				}
				
				// 顺利执行后输出提示
				System.out.print("DefectDaoImpl:  获取链表成功\n");
				dbc.close();
				
				//return 后不执行后面的语句
				return defectList;
			}
		} catch (Exception e) {

			// 抓到exception 给出提示
			//e.printStackTrace();
			System.out.print("DefectDaoImpl:  获取链表失败\n");
		}

		// result set 返回值为空，给出提示
		System.out.print("DefectDaoImpl:  执行查询成功哦，但是查不到结果");
		return null;
	}

}
